﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

public class DBCls
{
    // protected System.Data.SqlClient.SqlConnection mycnn = new SqlConnection();
    #region
    private bool _isTran = false;
    private string str_conn = "";

    private SqlTransaction _sqlTran;
    private SqlCommand _sqlCmd = new SqlCommand();
    private SqlConnection _sqlCon = new SqlConnection();


    bool is_oracle;

    #endregion
    public DBCls()
    {
        try
        {
            string str_conn=string.Empty;
            //str_conn = "Data Source=.\\SQLEXPRESS;Initial Catalog=FirMis;User ID=sa;Password=12345678";
            str_conn = ConfigurationManager.AppSettings["ConnectionString"].ToString();
            this.str_conn = str_conn;


            _sqlCon.ConnectionString = str_conn;// "server=gisdata;user id=mobile;pwd=moobile";



        }
        catch (Exception ex)
        {
            throw new ApplicationException("数据库链接错误");
        }
    }

    public  SqlDataReader ExecuteReader(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
    {
        if (_sqlCon.State.ToString().Equals("Closed"))
        {
            _sqlCon = new SqlConnection(str_conn);
           
        }
        try
        {

            _sqlCon.Open();
            SqlCommand cmd = new SqlCommand();

            // Call the private overload that takes an internally owned connection in place of the connection string
         
            return ExecuteReader(_sqlCon, null, commandType, commandText, commandParameters, SqlConnectionOwnership.Internal);
        }
        catch
        {
            // If we fail to return the SqlDatReader, we need to close the connection ourselves
            if (_sqlCon != null) _sqlCon.Close();
            throw;
        }

    }
    private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
    {
        if (connection == null) throw new ArgumentNullException("connection");

        bool mustCloseConnection = false;
        // Create a command and prepare it for execution
        SqlCommand cmd = new SqlCommand();
        try
        {
            PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

            // Create a reader
            SqlDataReader dataReader;

            // Call ExecuteReader with the appropriate CommandBehavior
            if (connectionOwnership == SqlConnectionOwnership.External)
            {
                dataReader = cmd.ExecuteReader();
            }
            else
            {
                dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }

            // Detach the SqlParameters from the command object, so they can be used again.
            // HACK: There is a problem here, the output parameter values are fletched 
            // when the reader is closed, so if the parameters are detached from the command
            // then the SqlReader can磘 set its values. 
            // When this happen, the parameters can磘 be used again in other command.
            bool canClear = true;
            foreach (SqlParameter commandParameter in cmd.Parameters)
            {
                if (commandParameter.Direction != ParameterDirection.Input)
                    canClear = false;
            }

            if (canClear)
            {
                cmd.Parameters.Clear();
            }

            return dataReader;
        }
        catch
        {
            if (mustCloseConnection)
                connection.Close();
            throw;
        }
    }
    private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, out bool mustCloseConnection)
    {
        if (command == null) throw new ArgumentNullException("command");
        if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");

        // If the provided connection is not open, we will open it
        if (connection.State != ConnectionState.Open)
        {
            mustCloseConnection = true;
            connection.Open();
        }
        else
        {
            mustCloseConnection = false;
        }

        // Associate the connection with the command
        command.Connection = connection;

        // Set the command text (stored procedure name or SQL statement)
        command.CommandText = commandText;

        // If we were provided a transaction, assign it
        if (transaction != null)
        {
            if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
            command.Transaction = transaction;
        }

        // Set the command type
        command.CommandType = commandType;

        // Attach the command parameters if they are provided
        if (commandParameters != null)
        {
            AttachParameters(command, commandParameters);
        }
        return;
    }
    private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
    {
        if (command == null) throw new ArgumentNullException("command");
        if (commandParameters != null)
        {
            foreach (SqlParameter p in commandParameters)
            {
                if (p != null)
                {
                    // Check for derived output value with no value assigned
                    if ((p.Direction == ParameterDirection.InputOutput ||
                        p.Direction == ParameterDirection.Input) &&
                        (p.Value == null))
                    {
                        p.Value = DBNull.Value;
                    }
                    command.Parameters.Add(p);
                }
            }
        }
    }
    private enum SqlConnectionOwnership
    {
        /// <summary>Connection is owned and managed by SqlHelper</summary>
        Internal,
        /// <summary>Connection is owned and managed by the caller</summary>
        External
    }
    #region 函数功能:执行一个sql语句
    ///<summary>
    /// 函数功能:执行一个sql语句
    /// 日期:2008年8月9日
    ///</summary>

    public bool ExcuteSql1_Tran(string mysql)
    {


        if (_sqlCon.State.ToString().Equals("Closed"))
        {
            _sqlCon = new SqlConnection(str_conn);
            _sqlCon.Open();
        }
        _sqlTran = _sqlCon.BeginTransaction(IsolationLevel.ReadCommitted);
        try
        {
            _sqlCmd = new SqlCommand();
            _sqlCmd.Connection = _sqlCon;
            _sqlCmd.Transaction = _sqlTran;
            _sqlCmd.CommandText = mysql;

            _sqlCmd.ExecuteNonQuery();
            _sqlTran.Commit();
            _sqlCon.Close();
            return true;
        }
        catch
        {
            _sqlTran.Rollback();
            _sqlCon.Close();
            return false;
            throw new ApplicationException("数据库链接错误");
           
        }
        
    }
    #endregion

    public bool ExcuteSql1(string mysql)
    {


        if (_sqlCon.State.ToString().Equals("Closed"))
        {
            _sqlCon = new SqlConnection(str_conn);
            _sqlCon.Open();
        }
        try
        {
            _sqlCmd = new SqlCommand();
            _sqlCmd.Connection = _sqlCon;
            _sqlCmd.Transaction = _sqlTran;
            _sqlCmd.CommandText = mysql;

            _sqlCmd.ExecuteNonQuery();
            _sqlCon.Close();
            return true;
        }
        catch
        {
            _sqlCon.Close();
            return false;
            throw new ApplicationException("数据库链接错误");

        }

    }

    #region 函数功能:执行两个sql语句
    ///<summary>
    /// 函数功能:执行两个sql语句
    /// 编写日期:2008年8月9日
    ///</summary>
    public bool ExcuteSql2(string mysql1, string mysql2)
    {

        if (_sqlCon.State.ToString().Equals("Closed"))
        {
            _sqlCon.Open();
        }
        _sqlTran = _sqlCon.BeginTransaction(IsolationLevel.ReadCommitted);
        try
        {
            _sqlCmd.Connection = _sqlCon;
            _sqlCmd.Transaction = _sqlTran;
            _sqlCmd.CommandText = mysql1;
            _sqlCmd.ExecuteNonQuery();

            _sqlCmd.CommandText = mysql2;
            _sqlCmd.ExecuteNonQuery();
            _sqlTran.Commit();
            _sqlCon.Close();
            return true;
        }
        catch
        {
            _sqlTran.Rollback();
            _sqlCon.Close();
            return false;
        }
    }

    #endregion

    #region 函数功能:执行三个sql语句
    ///<summary>
    /// 函数功能:执行三个sql语句
    /// 编写日期:2008年8月9日
    ///</summary>
    public bool ExcuteSql3(string mysql1, string mysql2, string mysql3)
    {

        if (_sqlCon.State.ToString().Equals("Closed"))
        {
            _sqlCon.Open();
        }

        _sqlTran = _sqlCon.BeginTransaction(IsolationLevel.ReadCommitted);
        try
        {
            _sqlCmd.Connection = _sqlCon;
            _sqlCmd.Transaction = _sqlTran;
            _sqlCmd.CommandText = mysql1;
            _sqlCmd.ExecuteNonQuery();

            _sqlCmd.CommandText = mysql2;
            _sqlCmd.ExecuteNonQuery();

            _sqlCmd.CommandText = mysql3;
            _sqlCmd.ExecuteNonQuery();

            _sqlTran.Commit();
            _sqlCon.Close();
            return true;
        }
        catch
        {
            _sqlTran.Rollback();
            _sqlCon.Close();
            return false;
        }
    }
    #endregion

    #region 函数功能:执行四个sql语句
    /// <summary>
    /// 函数功能:执行四个sql语句的事务
    /// 编写日期: 2008年8月9号
    /// </summary>
    public bool ExcuteSql4(string mysql1, string mysql2, string mysql3, string mysql4)
    {

        if (_sqlCon.State.ToString().Equals("Closed"))
        {
            _sqlCon.Open();
        }
        _sqlTran = _sqlCon.BeginTransaction(IsolationLevel.ReadCommitted);
        try
        {
            _sqlCmd.Transaction = _sqlTran;

            _sqlCmd.CommandText = mysql1;
            _sqlCmd.ExecuteNonQuery();

            _sqlCmd.CommandText = mysql2;
            _sqlCmd.ExecuteNonQuery();

            _sqlCmd.CommandText = mysql3;
            _sqlCmd.ExecuteNonQuery();

            _sqlCmd.CommandText = mysql4;
            _sqlCmd.ExecuteNonQuery();

            _sqlTran.Commit();
            _sqlCon.Close();
            return true;
        }
        catch
        {
            _sqlTran.Rollback();
            _sqlCon.Close();
            return false;
        }
    }
    #endregion

    #region 函数功能:查询某列数据返回一行
    /// <summary>
    ///  函数功能:查询某列数据，返回一个值
    /// 返回类型:字符类型
    /// 编写日期:2008年8月9日
    /// </summary>
    public string QueryFiled(string strsql, string FiledName)
    {
        string str_ReFileName = "";


        if (_sqlCon.State.ToString().Equals("Closed"))
        {
            _sqlCon.Open();
        }
        _sqlCmd.CommandText = strsql;
        SqlDataReader mydr = _sqlCmd.ExecuteReader();
        if (mydr.Read())
        {
            str_ReFileName = mydr["" + FiledName + ""].ToString().Trim();
        }
        mydr.Close();
        _sqlCon.Close();

        return str_ReFileName;
    }
    #endregion

    #region 函数功能:统计总条数
    /// <summary>
    /// 函数功能:统计总条数
    /// 返回类型:整型
    /// 编写时间:2008年8月9日
    /// </summary>
    public int ReSqlResultCount(string mysql)
    {
        int ReCount = 0;

        try
        {
            if (_sqlCon.State.ToString().Equals("Closed"))
            {
                _sqlCon = new SqlConnection(str_conn);
                _sqlCon.Open();
            }
            _sqlCmd = new SqlCommand();
            _sqlCmd.Connection = _sqlCon;
            _sqlCmd.CommandText = mysql;
            SqlDataReader mydr = _sqlCmd.ExecuteReader();
            while (mydr.Read())
            {
                ReCount++;
            }
        }
        catch
        {
            ReCount = 0;
        }
        finally
        {
            _sqlCon.Close();
        }

        return ReCount;
    }
    #endregion

    public SqlDataReader SqlReader(string mysql)
    {
        try
        {
            if (_sqlCon.State.ToString().Equals("Closed"))
            {
                _sqlCon = new SqlConnection(str_conn);
                _sqlCon.Open();
            }
            _sqlCmd = new SqlCommand();
            _sqlCmd.Connection = _sqlCon;
            _sqlCmd.CommandText = mysql;
            SqlDataReader mydr = _sqlCmd.ExecuteReader();
            return mydr;
            
        }
        catch(Exception ex)
        {
            throw new ApplicationException("数据库错误");
        }
        finally
        {
            //_sqlCon.Close();
        }

        
    }


    #region 函数功能:查询数据返回DataView
    ///<summary>
    /// 函数功能:查询数据返回DataView
    /// 返回类型:DataView
    /// 编写日期:2008年8月9日
    ///</summary>
    public System.Data.DataView QueryDataView(string mysql, string tablename)
    {
        if (_sqlCon.State.ToString().Equals("Closed"))
        {
            _sqlCon.Open();
        }
        DataSet myds = new DataSet();
        SqlDataAdapter oda = new SqlDataAdapter(mysql, _sqlCon);
        oda.Fill(myds, "" + tablename + "");
        DataView mydv = myds.Tables["" + tablename + ""].DefaultView;
        myds.Tables.Remove(tablename);
        _sqlCon.Close();
        return mydv;
    }
    #endregion

    #region 函数功能:查询数据返回DataTable
    ///<summary>
    /// 函数功能:查询数据返回DataTable
    /// 返回类型:DataTable
    /// 编写日期:2008年8月9日
    ///</summary>
    public System.Data.DataTable QueryDataTable(string mysql, string tablename)
    {

        DataTable mydt = new DataTable();
        try
        {
            if (_sqlCon.State.ToString().Equals("Closed"))
            {
               // _sqlCon = new SqlConnection(str_conn);
                _sqlCon.Open();
            }
            
            DataSet myds = new DataSet();
            SqlDataAdapter oda = new SqlDataAdapter(mysql, _sqlCon);
            oda.Fill(myds,tablename);
            mydt = myds.Tables[tablename];
            myds.Tables.Remove(tablename);
            _sqlCon.Close();
        }
        catch (Exception ex)
        {
            _sqlTran.Rollback();
            _sqlCon.Close();
            throw new ApplicationException("数据库链接错误");
        }
        return mydt;
    }
    #endregion

    #region 函数功能:查询数据返回某列集合
    /// <summary>
    /// 功能：函数功能:从数据库里返回某列数据的集合(下拉列表框)
    /// 返回类型:ArrayList
    /// 编写时间:2008年8月9日
    /// </summary>
    ///<param name="mysql">SQL语句</param>
    ///<param name="FileName">要查询的列名</param>
    public ArrayList QueryFiledColl(string mysql, string FileName)
    {
        string str_FiledValue = "";
        ArrayList alcount = new ArrayList();

        if (_sqlCon.State.ToString().Equals("Closed"))
        {
            _sqlCon.Open();
        }
        _sqlCmd.CommandText = mysql;
        SqlDataReader mydr = _sqlCmd.ExecuteReader();
        while (mydr.Read())
        {
            str_FiledValue = mydr["" + FileName + ""].ToString().Trim();
            alcount.Add(str_FiledValue);
        }
        mydr.Close();
        _sqlCon.Close();

        return alcount;
    }
    #endregion

    #region 函数功能:比较日期
    /// <summary>
    ///  函数功能:比较日期大小
    /// 返回类型:字符类型
    /// 编写日期:2008年8月9日
    /// </summary>
    public string bjdate(string kssj, string jssj)
    {
        string str_dx = "";
        System.DateTime dtks = System.Convert.ToDateTime(kssj);
        System.DateTime dtjs = System.Convert.ToDateTime(jssj);

        if (dtks > dtjs)
        {
            str_dx = "开始日期大于结束日期!";
        }
        return str_dx;
    }
    #endregion
    #region
    /// <summary>
    /// 执行sql语句+事物
    /// </summary>胡
    /// <param name="sqlString"></param>
    /// <returns></returns>
    public bool ExceltEdit(string sqlString)
    {
        bool _isSucess = false;

        try
        {
            if (_sqlCon.State.Equals(ConnectionState.Closed))
            {
                _sqlCon.Open();
            }
            _sqlCmd = _sqlCon.CreateCommand();
            if (_isTran)
            {
                _sqlCmd.Transaction = _sqlTran;
            }
            _sqlCmd.CommandText = sqlString;
            int n = _sqlCmd.ExecuteNonQuery();
            if (n > 0)
            {
                _isSucess = true;
            }
            return _isSucess;
        }
        catch (Exception ea)
        {
            if (_isTran)
            {
                _sqlTran.Rollback();
                _isTran = false;
            }
            throw ea;
        }
        finally
        {
            if (!_isTran)
            {
                _sqlCon.Close();
            }
        }

    }
    /// <summary>
    /// 开启事务
    /// </summary>
    /// <returns></returns>
    public bool Tran()
    {
        try
        {

            if (_sqlCon.State.Equals(ConnectionState.Closed))
            {
                _sqlCon.Open();
            }
            _sqlTran = _sqlCon.BeginTransaction(IsolationLevel.Serializable);


            _isTran = true;
            return _isTran;
        }
        catch (Exception ea)
        {
            throw ea;
        }

    }
    /// <summary>
    /// 提交事物
    /// </summary>
    public void Comimit()
    {
        try
        {

            if (_sqlCon.State.Equals(ConnectionState.Closed))
            {
                _sqlCon.Open();
            }
            if (_isTran)
            {
                _sqlTran.Commit();
            }


            _isTran = false;
        }
        catch (Exception ea)
        {
            throw ea;
        }
    }
    /// <summary>
    /// 释放系统资源
    /// </summary>
    public void Display()
    {
        try
        {

            _sqlCmd.Dispose();
            _sqlCon.Dispose();

        }
        catch (Exception ea)
        {
            throw ea;
        }
    }
    /// <summary>
    /// 回滚事物
    /// </summary>
    public void RollBack()
    {
        try
        {

            if (_sqlCon.State.Equals(ConnectionState.Closed))
            {
                _sqlCon.Open();
            }
            if (_isTran)
            {
                _sqlTran.Rollback();
            }

            _isTran = false;
        }
        catch (Exception ea)
        {
            throw ea;
        }
    }
    #endregion
}
